/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package de.herberlin.pss;

import java.io.File;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;

import org.apache.log4j.Logger;

import SQLite.Exception;
import de.herberlin.pss.model.Artist;
import de.herberlin.pss.model.Song;

/**
 * 
 * @author herberlin
 */
public class DatabaseManager {

	private Logger logger = Logger.getLogger(getClass());
	private String dbUrl = null;
	
	private static DatabaseManager _instance=null;
	
	public static synchronized DatabaseManager getInstance(File dbFile) {
		if (_instance==null) {
			_instance=new DatabaseManager(dbFile);
		}
		return _instance;
	}
	
	private DatabaseManager(File source) {
		dbUrl = source.getAbsolutePath();
		logger.debug("DatabaseUrl=" + dbUrl);
	}

	public synchronized  List<Song> getSongList() {
		ServiceCallback callback=new ServiceCallback(dbUrl);
		List<Properties> pl;
		try {
			pl = callback.getResult("select * from songTable");
		} catch (Exception e) {
			logger.error(e,e);
			throw new AppException(e);
		}
		List<Song> result=new LinkedList<Song>();
		for (Properties p : pl) {
			Song song=new Song(p);
			Integer aId=song.getInt(Song.Key.iArtistId);
			if (aId != null) {
				Artist artist=getArtist(aId);
				song.setArtist(artist);
			}
			result.add(song);
		}
		return result;
	}
	
	public synchronized Artist getArtist(int artistId) {
		Artist artist=null;
		ServiceCallback callback=new ServiceCallback(dbUrl);
		try {
			Properties props= callback.getFirstResult("select * from artistTable where iArtistId="+artistId);
			if (props != null) {
				artist=new Artist(props);
			}
		} catch (Exception e) {
			logger.error(e,e);
			throw new AppException(e);
		}
		
		return artist;
	}
	public synchronized Artist getArtist(String artistName) {
		Artist artist=null;
		ServiceCallback callback=new ServiceCallback(dbUrl);
		try {
			Properties props= callback.getFirstResult("select * from artistTable where cArtistName='"+artistName+"'");
			if (props != null) {
				artist=new Artist(props);
			}
		} catch (Exception e) {
			logger.error(e,e);
			throw new AppException(e);
		}
		
		return artist;
	}
	
	public static final int INT_NULL=-1;
	
	public synchronized  int getFreeId(String query) {
		ServiceCallback callback=new ServiceCallback(dbUrl);
		List<Integer> existingIds=new ArrayList<Integer>();
		try {
			List<Properties> propList= callback.getResult(query);
			for (Properties props: propList) {
				for (Object o: props.values()) {
					existingIds.add(new Integer(o.toString()));
				}
			}
		} catch (Exception e) {
			logger.error(e,e);
			throw new AppException(e);
		}
		
		for (int i=1;i<Integer.MAX_VALUE;i++) {
			if (!existingIds.contains(new Integer(i))){
				return i;
			}
		}
		return 0;
	}
	
	public synchronized void storeArtist(Artist artist) {
		if (artist.get(Artist.Key.iArtistId)==null && artist.get(Artist.Key.cArtistName)!=null) {
			
			Artist existing=getArtist(artist.get(Artist.Key.cArtistName));
			if (existing != null) {
				artist.set(Artist.Key.iArtistId, existing.get(Artist.Key.iArtistId));
				return;
			}
			
			int id=getFreeId("select iArtistId from "+artist.getTableName());
			if (id == INT_NULL) {
				throw new AppException("Cant find free ArtistId!");
			}
			artist.set(Artist.Key.iArtistId, id+"");
			String query=artist.createInsertQuery();
			
			try {
				new ServiceCallback(dbUrl).getResult(query);
			} catch (Exception e) {
				logger.error(e,e);
				throw new AppException(e);
			}
			
			
		}
	}
	
	public synchronized void storeSong(Song song) {
		
		if (song.get(Song.Key.iSongId) != null) {
			logger.warn("Song "+ song.get(Song.Key.iSongId)+ " already in database!");
			return;
		}
		
		Artist artist=song.getArtist();
		if ( artist != null) {
			storeArtist(artist);
			song.set(Song.Key.iArtistId, artist.get(Artist.Key.iArtistId));
		}
		try {
			int songId=getFreeId("select iSongId from songTable");
			song.set(Song.Key.iSongId, songId+"");
			new ServiceCallback(dbUrl).getResult(song.createInsertQuery());
		} catch (Exception e) {
			logger.error(e,e);
			throw new AppException(e);
		}
	}
	
	public synchronized void deleteSong(Song song) {
		
		if (song==null) {
			logger.warn("Song must not be null!");
		}
		
		try {
			// delete the artist if last
			if (song.getArtist()!=null) {
				Properties props=new ServiceCallback(dbUrl).getFirstResult("select count(iSongId) ct from songTable where iArtistId="+song.getArtist().getInt(Artist.Key.iArtistId));
				logger.debug(props);
				String count=props.getProperty("ct");
				if (count != null && new Integer(count).intValue()==1) {
					new ServiceCallback(dbUrl).getFirstResult(song.getArtist().createDeleteQuery());
				}
			}
			
			new ServiceCallback(dbUrl).getFirstResult(song.createDeleteQuery());
		} catch (Exception e) {
			logger.error(e,e);
			throw new AppException(e);
		}
	}
}
